﻿using System;
using System.Collections.Generic;
using KPIS.DBM;
using System.Configuration;
using System.Data;
using KPIS.GERP.GIMS.MODEL;

namespace KPIS.GERP.GIMS.DAL
{
    public class DispenseDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString); 
        ConvertNullable cn = new ConvertNullable();

        public DataSet GetDispense(Nullable<int> Sequence, Nullable<int> DispenseType)
        {
            DataSet ds = new DataSet();
            dbManager.Open();
            dbManager.CreateParameters(3);
            dbManager.AddParameters(0, "@Sequence", Sequence);
            dbManager.AddParameters(1, "@DispenseType", DispenseType);
            dbManager.AddParameters(2, "@record_status", SystemInfo._ActiveRecordStatus);

            string sql = "SELECT d.*, dt.DISPENSE_TYPE_NAME, js.JOB_SITE_NAME AS FROM_JOB_SITE_NAME"
                        + ", w.ICM_WH_NAME AS FROM_ICM_WH_NAME"
                        + " FROM ICM_DISPENSE d "
                        + " LEFT JOIN ICM_MAS_DESPENSE_TYPE dt ON d.DISPENSE_TYPE_SEQ = dt.DISPENSE_TYPE_SEQ"
                        + " LEFT JOIN ICM_MAS_WAREHOUSE w ON d.FROM_ICM_WH_SEQ = w.ICM_WH_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES js ON d.FROM_JOB_SITE_SEQ = js.JOB_SITE_SEQ"
                        + " WHERE d.RECORD_STATUS = @record_status"
                        + (DispenseType.HasValue ? " AND d.DISPENSE_TYPE_SEQ = @DispenseType" : "")
                        + (Sequence != null ? " AND d.DISPENSE_SEQ = @Sequence " : "");

            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }
        public void Insert(string dispense_desc,string dispense_date, string asset_code, Nullable<int> icm_wh_seq, Nullable<int> job_site_seq
            , int dispense_type_seq, string destination_location,string remark, int created_by)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(12);

                dbManager.AddParameters(0, "@dispense_desc", dispense_desc);
                dbManager.AddParameters(1, "@dispense_date", dispense_date.Length > 0 ? DateTime.Parse(dispense_date) : (DateTime?)null);
                dbManager.AddParameters(2, "@asset_code", asset_code);
                dbManager.AddParameters(3, "@icm_wh_seq", icm_wh_seq);
                dbManager.AddParameters(4, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(5, "@dispense_type_seq", dispense_type_seq);
                dbManager.AddParameters(6, "@destination_location", destination_location);
                dbManager.AddParameters(7, "@dispense_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(8, "@remark", remark);
                dbManager.AddParameters(9, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(10, "@created_by", created_by);
                dbManager.AddParameters(11, "@created_when", DateTime.Today);


                strSQL = "INSERT INTO icm_dispense"
                    + "(dispense_desc"
                    + ", dispense_date"
                    + ", asset_code"
                    + ", from_icm_wh_seq"
                    + ", from_job_site_seq"
                    + ", dispense_type_seq"
                    + ", destination_location"
                    + ", dispense_status"
                    + ", remark"
                    + ", record_status"
                    + ", created_by"
                    + ", created_when)"
                    + " VALUES (@dispense_desc"
                    + ", @dispense_date"
                    + ", @asset_code"
                    + ", @icm_wh_seq"
                    + ", @job_site_seq"
                    + ", @dispense_type_seq"
                    + ", @destination_location"
                    + ", @dispense_status"
                    + ", @remark"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void Update(int dispense_seq, string dispense_desc, string dispense_date, string asset_code, int icm_wh_seq
            , int job_site_seq, int dispense_type_seq, string destination_location,string remark, int updated_by)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(11);

                dbManager.AddParameters(0, "@dispense_desc", dispense_desc);
                dbManager.AddParameters(1, "@dispense_date", dispense_date);
                dbManager.AddParameters(2, "@asset_code", asset_code);
                dbManager.AddParameters(3, "@icm_wh_seq", icm_wh_seq);
                dbManager.AddParameters(4, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(5, "@dispense_type_seq", dispense_type_seq);
                dbManager.AddParameters(6, "@destination_location", destination_location);
                dbManager.AddParameters(7, "@dispense_seq", dispense_seq);
                dbManager.AddParameters(8, "@remark", remark);
                dbManager.AddParameters(9, "@updated_by", updated_by);
                dbManager.AddParameters(10, "@updated_when", DateTime.Today);

                strSQL = "UPDATE icm_despense"
                    + " SET dispense_desc = @dispense_desc"
                    + ", dispense_date = @dispense_date"
                    + ", asset_code = @asset_code"
                    + ", from_icm_wh_seq = @icm_wh_seq"
                    + ", from_job_site_seq = @job_site_seq"
                    + ", dispense_type_seq = @dispense_type_seq"
                    + ", destination_location = @destination_location"
                    + ", remark = @remark"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE dispense_seq = @dispense_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void Delete(int Sequence, int UpdatedBy)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@dispense_seq", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                strSQL = "UPDATE icm_despense"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE dispense_seq = @dispense_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Insert(string p, string p_2)
        {
            throw new NotImplementedException();
        }
    }
}
